from flask import Flask, render_template, request, redirect, url_for, flash, jsonify, make_response, send_file
from models.models import db, Grade, Project, Account, Parent, Student, Class, Registration, Payment, PaymentSummary, Semester
from flask_login import LoginManager, UserMixin, login_user, login_required, logout_user, current_user
from flask_migrate import Migrate
import os
from datetime import datetime
import csv
from io import StringIO, BytesIO
from utils.import_handler import ImportHandler
import pandas as pd
from markupsafe import Markup

app = Flask(__name__)
app.config['SECRET_KEY'] = os.urandom(24)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///school.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# 添加nl2br过滤器
@app.template_filter('nl2br')
def nl2br_filter(s):
    if not s:
        return s
    return Markup(s.replace('\n', '<br>'))

# 添加文件上传配置
UPLOAD_FOLDER = 'uploads'
ALLOWED_EXTENSIONS = {'xlsx', 'xls'}

if not os.path.exists(UPLOAD_FOLDER):
    os.makedirs(UPLOAD_FOLDER)

app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER

db.init_app(app)
migrate = Migrate(app, db)

# 创建所有数据库表
with app.app_context():
    db.create_all()

@app.route('/')
def index():
    return render_template('index.html')

# 学生管理
@app.route('/students')
def list_students():
    students = Student.query.all()
    return render_template('students/list.html', students=students)

@app.route('/students/add', methods=['GET', 'POST'])
def add_student():
    if request.method == 'POST':
        # 将日期字符串转换为date对象
        birth_date = datetime.strptime(request.form['birth_date'], '%Y-%m-%d').date()
        
        parent_id = request.form.get('parent_id')
        if parent_id:
            parent = Parent.query.get(parent_id)
            student = Student(
                name=request.form['name'],
                gender=request.form['gender'],
                birth_date=birth_date,  # 使用转换后的日期
                id_card=request.form['id_card'],
                address=request.form['address'],
                parent_id=parent_id,
                parent_name=parent.name,
                phone=parent.phone,
                parent_relation=request.form['parent_relation']
            )
        else:
            # 如果没有选择现有家长，创建新的家长记录
            parent = Parent(
                name=request.form['parent_name'],
                phone=request.form['phone']
            )
            db.session.add(parent)
            db.session.commit()
            
            student = Student(
                name=request.form['name'],
                gender=request.form['gender'],
                birth_date=birth_date,  # 使用转换后的日期
                id_card=request.form['id_card'],
                address=request.form['address'],
                parent_id=parent.id,
                parent_name=request.form['parent_name'],
                phone=request.form['phone'],
                parent_relation=request.form['parent_relation']
            )
        
        db.session.add(student)
        db.session.commit()
        flash('学生信息添加成功！')
        return redirect(url_for('list_students'))
    
    parents = Parent.query.all()
    return render_template('students/add.html', parents=parents)

@app.route('/students/add_ajax', methods=['POST'])
def add_student_ajax():
    try:
        # 将日期字符串转换为date对象
        birth_date = datetime.strptime(request.form['birth_date'], '%Y-%m-%d').date()
        
        # 创建新的家长记录
        parent = Parent(
            name=request.form['parent_name'],
            phone=request.form['phone']
        )
        db.session.add(parent)
        db.session.commit()
        
        # 创建新的学生记录
        student = Student(
            name=request.form['name'],
            gender=request.form['gender'],
            birth_date=birth_date,
            id_card=request.form['id_card'],
            address=request.form['address'],
            parent_id=parent.id,
            parent_name=request.form['parent_name'],
            phone=request.form['phone'],
            parent_relation=request.form['parent_relation']
        )
        db.session.add(student)
        db.session.commit()
        
        return jsonify({
            'success': True,
            'student': {
                'id': student.id,
                'name': student.name,
                'parent_name': student.parent_name,
                'parent_relation': student.parent_relation
            }
        })
    except Exception as e:
        db.session.rollback()
        return jsonify({
            'success': False,
            'message': str(e)
        }), 400

# 收费管理
@app.route('/payments')
def list_payments():
    # 获取所有收费记录中的最早和最晚日期
    date_range = db.session.query(
        db.func.min(Payment.date).label('min_date'),
        db.func.max(Payment.date).label('max_date')
    ).first()
    min_date = date_range.min_date if date_range.min_date else datetime.now()
    max_date = date_range.max_date if date_range.max_date else datetime.now()

    # 获取所有收费记录中的最小和最大金额
    amount_range = db.session.query(
        db.func.min(Payment.amount).label('min_amount'),
        db.func.max(Payment.amount).label('max_amount')
    ).first()
    min_amount = amount_range.min_amount if amount_range.min_amount else 0
    max_amount = amount_range.max_amount if amount_range.max_amount else 0

    # 获取下拉列表选项
    classes = Class.query.all()  # 获取所有班级
    years = db.session.query(Registration.year).distinct().order_by(Registration.year.desc()).all()  # 获取所有年度
    semesters = Semester.query.order_by(Semester.sort_order).all()  # 获取所有学期
    projects = Project.query.order_by(Project.sort_order).all()  # 获取所有收费项目
    accounts = Account.query.all()  # 获取所有收款账号

    # 构建查询
    query = Payment.query.order_by(Payment.date.desc(), Payment.id.desc())

    # 应用过滤条件
    if request.args.get('student'):
        query = query.filter(Payment.student.like(f"%{request.args.get('student')}%"))
    if request.args.get('class_name'):
        query = query.filter(Payment.class_name == request.args.get('class_name'))  # 改为精确匹配
    if request.args.get('year'):
        query = query.filter(Payment.year == request.args.get('year'))
    if request.args.get('semester'):
        query = query.filter(Payment.semester == request.args.get('semester'))
    if request.args.get('project'):
        query = query.filter(Payment.project == request.args.get('project'))  # 改为精确匹配
    if request.args.get('account'):
        query = query.filter(Payment.account == request.args.get('account'))  # 改为精确匹配
    
    # 日期过滤
    if request.args.get('start_date'):
        query = query.filter(Payment.date >= request.args.get('start_date'))
    if request.args.get('end_date'):
        query = query.filter(Payment.date <= request.args.get('end_date'))
    
    # 金额过滤
    if request.args.get('min_amount'):
        query = query.filter(Payment.amount >= float(request.args.get('min_amount')))
    if request.args.get('max_amount'):
        query = query.filter(Payment.amount <= float(request.args.get('max_amount')))

    # 获取过滤后的结果
    payments = query.all()

    return render_template('payments/list.html', 
                         payments=payments,
                         min_date=min_date,
                         max_date=max_date,
                         min_amount=min_amount,
                         max_amount=max_amount,
                         classes=classes,  # 传递选项数据到模板
                         years=years,
                         semesters=semesters,
                         projects=projects,
                         accounts=accounts)

@app.route('/payments/add', methods=['GET', 'POST'])
def add_payment():
    if request.method == 'POST':
        registration_id = request.form.get('registration_id')
        payment_date = request.form.get('payment_date')
        account = request.form.get('account')
        summary = request.form.get('summary')
        
        # 获取注册信息
        registration = Registration.query.get(registration_id)
        if not registration:
            flash('注册信息不存在', 'error')
            return redirect(url_for('list_payments'))
            
        # 获取所有项目和金额
        projects = request.form.getlist('projects[]')
        amounts = request.form.getlist('amounts[]')
        
        # 记录是否有有效的支付项目
        has_valid_payment = False
        
        # 遍历所有项目和金额
        for project, amount in zip(projects, amounts):
            # 检查金额是否有效（非空且大于0）
            try:
                amount_float = float(amount) if amount.strip() else 0
                if project and amount_float > 0:
                    # 创建支付记录
                    payment = Payment(
                        registration_id=registration_id,
                        student=registration.name,
                        class_name=f"{registration.grade} {registration.year} {registration.semester}",
                        year=registration.year,
                        semester=registration.semester,
                        grade=registration.grade,
                        project=project,
                        amount=amount_float,
                        date=datetime.strptime(payment_date, '%Y-%m-%d').date(),
                        account=account,
                        summary=summary
                    )
                    db.session.add(payment)
                    has_valid_payment = True
                    
                    # 更新或创建支付汇总记录
                    summary_record = PaymentSummary.query.get(registration_id)
                    if not summary_record:
                        summary_record = PaymentSummary(
                            registration_id=registration_id,
                            student=registration.name,
                            class_name=f"{registration.grade} {registration.year} {registration.semester}"
                        )
                        db.session.add(summary_record)
                    
                    # 更新项目金额
                    if not hasattr(summary_record, project):
                        setattr(summary_record, project, amount_float)
                    else:
                        current_amount = getattr(summary_record, project) or 0
                        setattr(summary_record, project, current_amount + amount_float)
            except ValueError:
                continue  # 跳过无效的金额输入
        
        if has_valid_payment:
            db.session.commit()
            flash('收费记录已添加', 'success')
        else:
            flash('没有有效的收费项目（金额必须大于0）', 'error')
        
        return redirect(url_for('list_payments'))
    
    # GET请求处理
    registrations = Registration.query.order_by(
        Registration.grade,
        Registration.year.desc(),
        Registration.semester.desc(),
        Registration.name
    ).all()
    
    # 按排序字段排序获取项目
    projects = Project.query.order_by(Project.sort_order.asc()).all()
    accounts = Account.query.order_by(Account.account).all()
    
    return render_template('payments/add.html',
                         registrations=registrations,
                         projects=projects,
                         accounts=accounts)

# 收费统计
@app.route('/payment_summary')
def payment_summary():
    # 获取所有收费记录中的最早和最晚日期
    date_range = db.session.query(
        db.func.min(Payment.date).label('min_date'),
        db.func.max(Payment.date).label('max_date')
    ).first()
    min_date = date_range.min_date if date_range.min_date else datetime.now()
    max_date = date_range.max_date if date_range.max_date else datetime.now()
    
    # 获取过滤参数
    selected_grade = request.args.get('grade', '')
    selected_year = request.args.get('year', '')
    selected_semester = request.args.get('semester', '')
    selected_student = request.args.get('student', '')
    
    # 设置默认日期为查询到的日期范围
    selected_start_date = request.args.get('start_date', '')
    selected_end_date = request.args.get('end_date', '')
    
    hide_zero = request.args.get('hide_zero') == '1'  # 获取勾选状态
    
    # 获取过滤选项数据
    grades = Grade.query.order_by(Grade.sort_order).all()
    years = db.session.query(Registration.year).distinct().order_by(Registration.year.desc()).all()
    years = [year[0] for year in years]  # 转换为列表
    semesters = Semester.query.order_by(Semester.sort_order).all()
    students = Student.query.order_by(Student.name).all()  # 获取所有学生列表
    
    # 构建注册记录查询
    registrations_query = Registration.query
    
    # 应用过滤条件
    if selected_grade:
        registrations_query = registrations_query.filter(Registration.grade == selected_grade)
    if selected_year:
        registrations_query = registrations_query.filter(Registration.year == selected_year)
    if selected_semester:
        registrations_query = registrations_query.filter(Registration.semester == selected_semester)
    if selected_student:
        registrations_query = registrations_query.filter(Registration.student == selected_student)
    
    # 获取过滤后的注册记录
    registrations = registrations_query.all()
    
    # 获取所有收费项目并按排序字段排序
    all_projects = Project.query.order_by(Project.sort_order).all()
    project_names = [project.name for project in all_projects]
    
    # 创建收费统计结果
    summaries = []
    total_amount = 0
    
    for registration in registrations:
        # 构建收费记录查询
        payments_query = Payment.query.filter_by(registration_id=registration.id)
        
        # 添加日期过滤条件
        if selected_start_date:
            payments_query = payments_query.filter(Payment.date >= selected_start_date)
        if selected_end_date:
            payments_query = payments_query.filter(Payment.date <= selected_end_date)
        
        # 获取过滤后的收费记录
        payments = payments_query.all()
        
        # 如果没有收费记录，创建一个空的统计记录
        summary = {
            'registration_id': registration.id,
            'student': registration.student,
            'class_name': registration.grade + ' ' + registration.year + ' ' + registration.semester,
            'fees': {},
            'total': 0
        }
        
        # 统计每个项目的收费金额
        for payment in payments:
            if payment.project not in summary['fees']:
                summary['fees'][payment.project] = 0
            summary['fees'][payment.project] += payment.amount
            summary['total'] += payment.amount
            total_amount += payment.amount
        
        # 如果勾选了隐藏无收费记录且当前记录总金额为0，则跳过
        if hide_zero and summary['total'] == 0:
            continue
            
        summaries.append(summary)
    
    # 计算人均收费（使用实际显示的记录数）
    student_count = len(summaries)  # 使用过滤后的summaries长度
    avg_amount = total_amount / student_count if student_count > 0 else 0
    
    # 将项目列表转换为模板所需的格式
    projects = [{'name': name} for name in project_names]
    
    return render_template('payments/summary.html', 
                         summaries=summaries, 
                         projects=projects,
                         total_amount=total_amount,
                         student_count=student_count,
                         avg_amount=avg_amount,
                         grades=grades,
                         years=years,
                         semesters=semesters,
                         students=students,
                         selected_grade=selected_grade,
                         selected_year=selected_year,
                         selected_semester=selected_semester,
                         selected_student=selected_student,
                         selected_start_date=selected_start_date,
                         selected_end_date=selected_end_date,
                         min_date=min_date,
                         max_date=max_date,
                         hide_zero=hide_zero)  # 传递勾选状态到模板

# 家长管理
@app.route('/parents')
def list_parents():
    parents = Parent.query.all()
    return render_template('parents/list.html', parents=parents)

@app.route('/parents/add', methods=['GET', 'POST'])
def add_parent():
    if request.method == 'POST':
        parent = Parent(
            name=request.form['name'],
            phone=request.form['phone']
        )
        db.session.add(parent)
        db.session.commit()
        flash('家长信息添加成功！')
        return redirect(url_for('list_parents'))
    return render_template('parents/add.html')

@app.route('/parents/<int:id>/edit', methods=['GET', 'POST'])
def edit_parent(id):
    parent = Parent.query.get_or_404(id)
    if request.method == 'POST':
        parent.name = request.form['name']
        parent.phone = request.form['phone']
        db.session.commit()
        flash('家长信息更新成功！')
        return redirect(url_for('list_parents'))
    return render_template('parents/edit.html', parent=parent)

@app.route('/parents/<int:id>/delete', methods=['POST'])
def delete_parent(id):
    parent = Parent.query.get_or_404(id)
    if parent.students:
        flash('该家长有关联的学生信息，无法删除！')
    else:
        db.session.delete(parent)
        db.session.commit()
        flash('家长信息已删除！')
    return redirect(url_for('list_parents'))

# 开班管理
@app.route('/classes')
def list_classes():
    classes = Class.query.all()
    return render_template('classes/list.html', classes=classes)

@app.route('/classes/add', methods=['GET', 'POST'])
def add_class():
    if request.method == 'POST':
        year = request.form['year']
        grade = request.form['grade']
        semester = request.form['semester']
        
        # 检查是否已存在相同的班级
        existing_class = Class.query.filter_by(
            grade=grade,
            year=year,
            semester=semester
        ).first()
        
        if existing_class:
            flash('该班级已存在！', 'error')
            return redirect(url_for('add_class'))
        
        class_obj = Class(
            grade=grade,
            year=year,  # 直接保存年份
            semester=semester
        )
        db.session.add(class_obj)
        db.session.commit()
        flash('开班信息添加成功！')
        return redirect(url_for('list_classes'))
    
    current_year = datetime.now().year
    grades = Grade.query.order_by(Grade.sort_order).all()
    semesters = Semester.query.order_by(Semester.sort_order).all()
    return render_template('classes/add.html', current_year=current_year, grades=grades, semesters=semesters)

@app.route('/classes/<int:id>/edit', methods=['GET', 'POST'])
def edit_class(id):
    class_obj = Class.query.get_or_404(id)
    if request.method == 'POST':
        class_obj.grade = request.form['grade']
        class_obj.year = request.form['year']  # 直接保存年份
        class_obj.semester = request.form['semester']
        db.session.commit()
        flash('开班信息更新成功！')
        return redirect(url_for('list_classes'))
    
    grades = Grade.query.order_by(Grade.sort_order).all()
    semesters = Semester.query.order_by(Semester.sort_order).all()
    return render_template('classes/edit.html', class_obj=class_obj, grades=grades, semesters=semesters)

@app.route('/classes/<int:id>/view')
def view_class(id):
    class_obj = Class.query.get_or_404(id)
    # 获取该班级的所有注册学生
    registrations = Registration.query.filter_by(class_id=id).all()
    return render_template('classes/view.html', class_obj=class_obj, registrations=registrations)

@app.route('/classes/<int:id>/delete', methods=['POST'])
def delete_class(id):
    class_obj = Class.query.get_or_404(id)
    try:
        # 检查是否有关联的注册记录
        registration = Registration.query.filter_by(class_id=id).first()
        if registration:
            flash('该班级已有学生注册，无法删除！', 'error')
            return redirect(url_for('list_classes'))
            
        db.session.delete(class_obj)
        db.session.commit()
        flash('班级删除成功！')
    except Exception as e:
        db.session.rollback()
        flash('删除失败：' + str(e), 'error')
    return redirect(url_for('list_classes'))

# 注册管理
@app.route('/registrations')
def list_registrations():
    registrations = Registration.query.all()
    return render_template('registrations/list.html', registrations=registrations)

@app.route('/registrations/add', methods=['GET', 'POST'])
def add_registration():
    if request.method == 'POST':
        student_id = request.form.get('student_id')
        class_id = request.form.get('class_id')
        
        # 检查是否已存在相同的注册记录
        existing_registration = Registration.query.filter_by(
            student_id=student_id,
            class_id=class_id
        ).first()
        
        if existing_registration:
            flash('该学生已经注册了这个班级！', 'error')
            return redirect(url_for('add_registration'))
        
        # 获取学生和班级信息
        student = Student.query.get(student_id)
        class_obj = Class.query.get(class_id)
        
        # 创建注册记录
        registration = Registration(
            student_id=student.id,
            class_id=class_obj.id,
            year=class_obj.year,
            semester=class_obj.semester,
            grade=class_obj.grade,
            name=student.name,
            gender=student.gender,
            parent_relation=student.parent_relation,
            student=student.name
        )
        db.session.add(registration)
        db.session.commit()
        flash('注册信息添加成功！')
        return redirect(url_for('list_registrations'))
    
    students = Student.query.all()
    classes = Class.query.all()
    return render_template('registrations/add.html', students=students, classes=classes)

@app.route('/registrations/<int:id>/edit', methods=['GET', 'POST'])
def edit_registration(id):
    registration = Registration.query.get_or_404(id)
    if request.method == 'POST':
        # 获取学生和班级信息
        student = Student.query.get(request.form['student_id'])
        class_obj = Class.query.get(request.form['class_id'])
        
        registration.student_id = student.id
        registration.class_id = class_obj.id
        registration.year = class_obj.year
        registration.semester = class_obj.semester
        registration.grade = class_obj.grade
        registration.name = student.name
        registration.gender = student.gender
        registration.parent_relation = student.parent_relation
        registration.student = student.name
        
        db.session.commit()
        flash('注册信息更新成功！')
        return redirect(url_for('list_registrations'))
    
    students = Student.query.all()
    classes = Class.query.all()
    return render_template('registrations/edit.html', registration=registration, students=students, classes=classes)

@app.route('/registrations/<int:id>/delete', methods=['POST'])
def delete_registration(id):
    registration = Registration.query.get_or_404(id)
    try:
        # 检查是否有关联的收费记录
        payment = Payment.query.filter_by(registration_id=id).first()
        if payment:
            flash('该注册记录已有收费记录，无法删除！', 'error')
            return redirect(url_for('list_registrations'))
            
        # 删除收费汇总记录
        summary = PaymentSummary.query.filter_by(registration_id=id).first()
        if summary:
            db.session.delete(summary)
            
        # 删除注册记录
        db.session.delete(registration)
        db.session.commit()
        flash('注册记录已删除！')
    except Exception as e:
        db.session.rollback()
        flash('删除失败：' + str(e), 'error')
    return redirect(url_for('list_registrations'))

# 年级管理
@app.route('/grades')
def list_grades():
    grades = Grade.query.order_by(Grade.sort_order).all()
    return render_template('grades/list.html', grades=grades)

@app.route('/grades/add', methods=['GET', 'POST'])
def add_grade():
    if request.method == 'POST':
        grade = Grade(
            name=request.form['name'],
            sort_order=int(request.form['sort_order'])
        )
        db.session.add(grade)
        db.session.commit()
        flash('年级信息添加成功！')
        return redirect(url_for('list_grades'))
    return render_template('grades/add.html')

@app.route('/grades/<int:id>/edit', methods=['GET', 'POST'])
def edit_grade(id):
    grade = Grade.query.get_or_404(id)
    if request.method == 'POST':
        grade.name = request.form['name']
        grade.sort_order = int(request.form['sort_order'])
        db.session.commit()
        flash('年级信息更新成功！')
        return redirect(url_for('list_grades'))
    return render_template('grades/edit.html', grade=grade)

@app.route('/grades/<int:id>/delete', methods=['POST'])
def delete_grade(id):
    grade = Grade.query.get_or_404(id)
    try:
        db.session.delete(grade)
        db.session.commit()
        flash('年级信息已删除！')
    except:
        db.session.rollback()
        flash('该年级已被使用，无法删除！')
    return redirect(url_for('list_grades'))

# 学期管理
@app.route('/semesters')
def list_semesters():
    semesters = Semester.query.order_by(Semester.sort_order).all()
    return render_template('semesters/list.html', semesters=semesters)

@app.route('/semesters/add', methods=['GET', 'POST'])
def add_semester():
    if request.method == 'POST':
        semester = Semester(
            name=request.form['name'],
            sort_order=int(request.form['sort_order'])
        )
        db.session.add(semester)
        db.session.commit()
        flash('学期信息添加成功！')
        return redirect(url_for('list_semesters'))
    return render_template('semesters/add.html')

@app.route('/semesters/<int:id>/edit', methods=['GET', 'POST'])
def edit_semester(id):
    semester = Semester.query.get_or_404(id)
    if request.method == 'POST':
        semester.name = request.form['name']
        semester.sort_order = int(request.form['sort_order'])
        db.session.commit()
        flash('学期信息更新成功！')
        return redirect(url_for('list_semesters'))
    return render_template('semesters/edit.html', semester=semester)

@app.route('/semesters/<int:id>/delete', methods=['POST'])
def delete_semester(id):
    semester = Semester.query.get_or_404(id)
    try:
        db.session.delete(semester)
        db.session.commit()
        flash('学期信息已删除！')
    except:
        db.session.rollback()
        flash('该学期已被使用，无法删除！')
    return redirect(url_for('list_semesters'))

# 收款账号管理
@app.route('/accounts')
def list_accounts():
    accounts = Account.query.all()
    return render_template('accounts/list.html', accounts=accounts)

@app.route('/accounts/add', methods=['GET', 'POST'])
def add_account():
    if request.method == 'POST':
        # 检查是否已存在相同的账号
        existing_account = Account.query.filter_by(account=request.form['name']).first()
        if existing_account:
            flash('该收款账号已存在！', 'error')
            return redirect(url_for('add_account'))
            
        account = Account(
            account=request.form['name']  # 使用表单中的name字段值
        )
        db.session.add(account)
        db.session.commit()
        flash('收款账号添加成功！')
        return redirect(url_for('list_accounts'))
    return render_template('accounts/add.html')

@app.route('/accounts/<int:id>/edit', methods=['GET', 'POST'])
def edit_account(id):
    account = Account.query.get_or_404(id)
    if request.method == 'POST':
        # 检查是否存在相同的账号（排除当前编辑的账号）
        existing_account = Account.query.filter(
            Account.account == request.form['name'],
            Account.id != id
        ).first()
        if existing_account:
            flash('该收款账号已存在！', 'error')
            return redirect(url_for('edit_account', id=id))
            
        account.account = request.form['name']  # 使用表单中的name字段值
        db.session.commit()
        flash('收款账号更新成功！')
        return redirect(url_for('list_accounts'))
    return render_template('accounts/edit.html', account=account)

@app.route('/accounts/<int:id>/delete', methods=['POST'])
def delete_account(id):
    account = Account.query.get_or_404(id)
    try:
        # 检查是否有关联的收费记录
        payment = Payment.query.filter_by(account=account.account).first()  # 使用account字段
        if payment:
            flash('该账号已有关联的收费记录，无法删除！', 'error')
        else:
            db.session.delete(account)
            db.session.commit()
            flash('收款账号已删除！')
    except Exception as e:
        db.session.rollback()
        flash('删除失败：' + str(e), 'error')
    return redirect(url_for('list_accounts'))

# 收费项目管理
@app.route('/projects')
def list_projects():
    projects = Project.query.order_by(Project.sort_order).all()
    return render_template('projects/list.html', projects=projects)

@app.route('/projects/add', methods=['GET', 'POST'])
def add_project():
    if request.method == 'POST':
        name = request.form.get('name')
        sort_order = request.form.get('sort_order')
        
        project = Project(name=name, sort_order=sort_order)
        db.session.add(project)
        db.session.commit()
        
        flash('收费项目添加成功！', 'success')
        return redirect(url_for('list_projects'))
    
    return render_template('projects/add.html')

@app.route('/projects/<int:id>/edit', methods=['GET', 'POST'])
def edit_project(id):
    project = Project.query.get_or_404(id)
    
    if request.method == 'POST':
        project.name = request.form.get('name')
        project.sort_order = request.form.get('sort_order')
        db.session.commit()
        
        flash('收费项目更新成功！', 'success')
        return redirect(url_for('list_projects'))
    
    return render_template('projects/edit.html', project=project)

@app.route('/projects/<int:id>/delete', methods=['POST'])
def delete_project(id):
    project = Project.query.get_or_404(id)
    db.session.delete(project)
    db.session.commit()
    
    flash('收费项目删除成功！', 'success')
    return redirect(url_for('list_projects'))

# 学生管理
@app.route('/students/<int:id>/view')
def view_student(id):
    student = Student.query.get_or_404(id)
    # 获取该学生的所有注册记录
    registrations = Registration.query.filter_by(student_id=id).all()
    # 获取该学生的所有收费记录
    payments = Payment.query.join(Registration).filter(Registration.student_id == id).all()
    return render_template('students/view.html', student=student, registrations=registrations, payments=payments)

@app.route('/students/<int:id>/edit', methods=['GET', 'POST'])
def edit_student(id):
    student = Student.query.get_or_404(id)
    if request.method == 'POST':
        # 将日期字符串转换为date对象
        birth_date = datetime.strptime(request.form['birth_date'], '%Y-%m-%d').date()
        
        student.name = request.form['name']
        student.gender = request.form['gender']
        student.birth_date = birth_date
        student.id_card = request.form['id_card']
        student.address = request.form['address']
        student.parent_relation = request.form['parent_relation']
        
        # 如果选择了现有家长
        parent_id = request.form.get('parent_id')
        if parent_id:
            parent = Parent.query.get(parent_id)
            student.parent_id = parent_id
            student.parent_name = parent.name
            student.phone = parent.phone
        else:
            # 如果是新的家长信息
            parent = Parent(
                name=request.form['parent_name'],
                phone=request.form['phone']
            )
            db.session.add(parent)
            db.session.commit()
            
            student.parent_id = parent.id
            student.parent_name = request.form['parent_name']
            student.phone = request.form['phone']
        
        db.session.commit()
        flash('学生信息更新成功！')
        return redirect(url_for('list_students'))
    
    parents = Parent.query.all()
    return render_template('students/edit.html', student=student, parents=parents)

@app.route('/students/<int:id>/delete', methods=['POST'])
def delete_student(id):
    student = Student.query.get_or_404(id)
    try:
        # 检查是否有关联的注册记录
        registration = Registration.query.filter_by(student_id=id).first()
        if registration:
            flash('该学生已有注册记录，无法删除！', 'error')
            return redirect(url_for('list_students'))
            
        db.session.delete(student)
        db.session.commit()
        flash('学生信息已删除！')
    except Exception as e:
        db.session.rollback()
        flash('删除失败：' + str(e), 'error')
    return redirect(url_for('list_students'))

# 收费管理
@app.route('/payments/<int:id>/edit', methods=['GET', 'POST'])
def edit_payment(id):
    payment = Payment.query.get_or_404(id)
    if request.method == 'POST':
        old_project = payment.project
        old_amount = payment.amount
        
        payment.project = request.form['project']
        payment.amount = float(request.form['amount'])
        payment.account = request.form['account']
        payment.summary = request.form['summary']
        payment.date = datetime.strptime(request.form['payment_date'], '%Y-%m-%d').date()
        
        # 更新收费汇总记录
        summary = PaymentSummary.query.get(payment.registration_id)
        if summary:
            # 减去旧的金额
            summary.update_fee(old_project, old_amount, is_add=False)
            # 加上新的金额
            summary.update_fee(payment.project, payment.amount)
        
        db.session.commit()
        flash('收费记录更新成功！')
        return redirect(url_for('list_payments'))
    
    registrations = Registration.query.all()
    accounts = Account.query.all()
    projects = Project.query.order_by(Project.sort_order).all()
    return render_template('payments/edit.html', payment=payment, registrations=registrations, accounts=accounts, projects=projects)

@app.route('/payments/<int:id>/delete', methods=['POST'])
def delete_payment(id):
    payment = Payment.query.get_or_404(id)
    try:
        # 更新收费汇总记录
        summary = PaymentSummary.query.get(payment.registration_id)
        if summary:
            # 减去要删除的金额
            summary.update_fee(payment.project, payment.amount, is_add=False)
        
        db.session.delete(payment)
        db.session.commit()
        flash('收费记录已删除！')
    except Exception as e:
        db.session.rollback()
        flash('删除失败：' + str(e), 'error')
    return redirect(url_for('list_payments'))

# 导出收费统计
@app.route('/payment_summary/export')
def export_payment_summary():
    # 获取过滤参数
    selected_grade = request.args.get('grade', '')
    selected_year = request.args.get('year', '')
    selected_semester = request.args.get('semester', '')
    selected_student = request.args.get('student', '')
    selected_start_date = request.args.get('start_date', '')
    selected_end_date = request.args.get('end_date', '')
    hide_zero = request.args.get('hide_zero') == '1'
    
    # 构建注册记录查询
    registrations_query = Registration.query
    
    # 应用过滤条件
    if selected_grade:
        registrations_query = registrations_query.filter(Registration.grade == selected_grade)
    if selected_year:
        registrations_query = registrations_query.filter(Registration.year == selected_year)
    if selected_semester:
        registrations_query = registrations_query.filter(Registration.semester == selected_semester)
    if selected_student:
        registrations_query = registrations_query.filter(Registration.student == selected_student)
    
    # 获取过滤后的注册记录
    registrations = registrations_query.all()
    
    # 获取所有收费项目并按排序字段排序
    all_projects = Project.query.order_by(Project.sort_order).all()
    project_names = [project.name for project in all_projects]
    
    # 创建CSV文件
    si = StringIO()
    writer = csv.writer(si, dialect='excel')
    
    # 写入BOM标记，确保Excel正确识别UTF-8编码
    si.write('\ufeff')
    
    # 写入表头
    headers = ['注册编号', '学生', '班级'] + project_names + ['合计']
    writer.writerow(headers)
    
    # 写入数据
    for registration in registrations:
        # 构建收费记录查询
        payments_query = Payment.query.filter_by(registration_id=registration.id)
        
        # 添加日期过滤条件
        if selected_start_date:
            try:
                start_date = datetime.strptime(selected_start_date, '%Y-%m-%d').date()
                payments_query = payments_query.filter(db.func.date(Payment.date) >= start_date)
            except ValueError:
                pass
                
        if selected_end_date:
            try:
                end_date = datetime.strptime(selected_end_date, '%Y-%m-%d').date()
                payments_query = payments_query.filter(db.func.date(Payment.date) <= end_date)
            except ValueError:
                pass
        
        # 获取过滤后的收费记录
        payments = payments_query.all()
        
        # 统计每个项目的收费金额
        fees = {}
        total = 0
        for payment in payments:
            if payment.project not in fees:
                fees[payment.project] = 0
            fees[payment.project] += payment.amount
            total += payment.amount
        
        # 如果勾选了隐藏无收费记录且当前记录总金额为0，则跳过
        if hide_zero and total == 0:
            continue
        
        # 准备行数据
        row = [
            registration.id,
            registration.student,
            registration.grade + ' ' + registration.year + ' ' + registration.semester
        ]
        
        # 添加每个项目的金额
        for project in project_names:
            row.append('%.2f' % fees.get(project, 0))
        
        # 添加合计
        row.append('%.2f' % total)
        
        writer.writerow(row)
    
    # 生成响应
    output = si.getvalue()
    si.close()
    
    # 生成文件名（使用英文和数字）
    filename = 'payment_summary_{}.csv'.format(datetime.now().strftime('%Y%m%d_%H%M%S'))
    
    # 设置响应头
    response = make_response(output.encode('utf-8'))
    response.headers['Content-Type'] = 'text/csv; charset=utf-8'
    response.headers['Content-Disposition'] = 'attachment; filename="{}"'.format(filename)
    
    return response

# 导出收费记录明细
@app.route('/payments/export')
def export_payments():
    # 构建查询
    query = Payment.query.order_by(Payment.date.desc(), Payment.id.desc())

    # 应用过滤条件
    if request.args.get('student'):
        query = query.filter(Payment.student.like(f"%{request.args.get('student')}%"))
    if request.args.get('class_name'):
        query = query.filter(Payment.class_name == request.args.get('class_name'))
    if request.args.get('year'):
        query = query.filter(Payment.year == request.args.get('year'))
    if request.args.get('semester'):
        query = query.filter(Payment.semester == request.args.get('semester'))
    if request.args.get('project'):
        query = query.filter(Payment.project == request.args.get('project'))
    if request.args.get('account'):
        query = query.filter(Payment.account == request.args.get('account'))
    
    # 日期过滤
    if request.args.get('start_date'):
        query = query.filter(Payment.date >= request.args.get('start_date'))
    if request.args.get('end_date'):
        query = query.filter(Payment.date <= request.args.get('end_date'))
    
    # 金额过滤
    if request.args.get('min_amount'):
        query = query.filter(Payment.amount >= float(request.args.get('min_amount')))
    if request.args.get('max_amount'):
        query = query.filter(Payment.amount <= float(request.args.get('max_amount')))

    # 获取过滤后的结果
    payments = query.all()
    
    # 创建CSV文件
    si = StringIO()
    writer = csv.writer(si, dialect='excel')
    
    # 写入BOM标记，确保Excel正确识别UTF-8编码
    si.write('\ufeff')
    
    # 写入表头
    headers = ['日期', '学生', '班级', '年度', '学期', '收费项目', '金额', '收款账号', '摘要']
    writer.writerow(headers)
    
    # 写入数据
    for payment in payments:
        row = [
            payment.date.strftime('%Y-%m-%d'),
            payment.student,
            payment.class_name,
            payment.year,
            payment.semester,
            payment.project,
            '%.2f' % payment.amount,
            payment.account,
            payment.summary or ''
        ]
        writer.writerow(row)
    
    # 生成响应
    output = si.getvalue()
    si.close()
    
    # 生成文件名
    filename = 'payment_details_{}.csv'.format(datetime.now().strftime('%Y%m%d_%H%M%S'))
    
    # 设置响应头
    response = make_response(output.encode('utf-8'))
    response.headers['Content-Type'] = 'text/csv; charset=utf-8'
    response.headers['Content-Disposition'] = 'attachment; filename="{}"'.format(filename)
    
    return response

# 退费管理
@app.route('/refund', methods=['GET', 'POST'])
def add_refund():
    if request.method == 'POST':
        registration_id = request.form.get('registration_id')
        payment_date = request.form.get('payment_date')
        account = request.form.get('account')
        summary = request.form.get('summary')
        
        # 获取注册信息
        registration = Registration.query.get(registration_id)
        if not registration:
            flash('注册信息不存在', 'error')
            return redirect(url_for('list_payments'))
            
        # 获取所有项目和金额
        projects = request.form.getlist('projects[]')
        amounts = request.form.getlist('amounts[]')
        
        # 记录是否有有效的退费项目
        has_valid_refund = False
        
        # 遍历所有项目和金额
        for project, amount in zip(projects, amounts):
            # 检查金额是否有效（非空且小于0）
            try:
                amount_float = float(amount) if amount.strip() else 0
                if project and amount_float < 0:  # 确保金额是负数
                    # 创建退费记录
                    payment = Payment(
                        registration_id=registration_id,
                        student=registration.student,
                        class_name=f"{registration.grade} {registration.year} {registration.semester}",
                        year=registration.year,
                        semester=registration.semester,
                        grade=registration.grade,
                        project=project,
                        amount=amount_float,  # 保存负数金额
                        date=datetime.strptime(payment_date, '%Y-%m-%d').date(),
                        account=account,
                        summary=summary or "退费"
                    )
                    db.session.add(payment)
                    has_valid_refund = True
                    
                    # 更新或创建支付汇总记录
                    summary_record = PaymentSummary.query.get(registration_id)
                    if not summary_record:
                        summary_record = PaymentSummary(
                            registration_id=registration_id,
                            student=registration.student,
                            class_name=f"{registration.grade} {registration.year} {registration.semester}"
                        )
                        db.session.add(summary_record)
                    
                    # 更新项目金额
                    if not hasattr(summary_record, project):
                        setattr(summary_record, project, amount_float)
                    else:
                        current_amount = getattr(summary_record, project) or 0
                        setattr(summary_record, project, current_amount + amount_float)
            except ValueError:
                continue  # 跳过无效的金额输入
        
        if has_valid_refund:
            db.session.commit()
            flash('退费记录已添加', 'success')
        else:
            flash('没有有效的退费项目（金额必须小于0）', 'error')
        
        return redirect(url_for('list_payments'))
    
    # GET请求处理
    registrations = Registration.query.order_by(
        Registration.grade,
        Registration.year.desc(),
        Registration.semester.desc(),
        Registration.name
    ).all()
    
    # 按排序字段排序获取项目
    projects = Project.query.order_by(Project.sort_order.asc()).all()
    accounts = Account.query.order_by(Account.account).all()
    
    return render_template('payments/add_refund.html',
                         registrations=registrations,
                         projects=projects,
                         accounts=accounts,
                         today=datetime.now().strftime('%Y-%m-%d'))

@app.route('/refund/batch', methods=['POST'])
def batch_refund():
    if 'csv_file' not in request.files:
        flash('没有上传文件', 'error')
        return redirect(url_for('add_refund'))
    
    csv_file = request.files['csv_file']
    if csv_file.filename == '':
        flash('没有选择文件', 'error')
        return redirect(url_for('add_refund'))
    
    # 获取默认退款账号
    default_account = request.form.get('batch_account')
    if not default_account:
        accounts = Account.query.first()
        default_account = accounts.account if accounts else ""
    
    # 读取CSV文件
    try:
        csv_content = csv_file.read().decode('utf-8-sig')
        csv_reader = csv.reader(StringIO(csv_content))
        headers = next(csv_reader)  # 跳过表头
        
        # 必须包含的列
        required_columns = ['学生姓名', '班级名称', '退费项目', '退费金额']
        
        # 检查必要的列是否存在
        for column in required_columns:
            if column not in headers:
                flash(f'CSV文件缺少必要的列：{column}', 'error')
                return redirect(url_for('add_refund'))
        
        # 获取列索引
        student_index = headers.index('学生姓名')
        class_index = headers.index('班级名称')
        project_index = headers.index('退费项目')
        amount_index = headers.index('退费金额')
        
        # 可选列索引
        date_index = headers.index('退费日期') if '退费日期' in headers else -1
        account_index = headers.index('退款账号') if '退款账号' in headers else -1
        summary_index = headers.index('摘要') if '摘要' in headers else -1
        
        today = datetime.now().strftime('%Y-%m-%d')
        success_count = 0
        error_count = 0
        
        for row in csv_reader:
            if len(row) < max(student_index, class_index, project_index, amount_index) + 1:
                error_count += 1
                continue
                
            student_name = row[student_index].strip()
            class_name = row[class_index].strip()
            project_name = row[project_index].strip()
            
            # 处理金额
            try:
                amount = float(row[amount_index])
                # 确保金额为负数
                if amount >= 0:
                    amount = -amount  # 转为负数
            except ValueError:
                error_count += 1
                continue
            
            # 处理日期
            if date_index >= 0 and date_index < len(row) and row[date_index].strip():
                try:
                    payment_date = datetime.strptime(row[date_index], '%Y-%m-%d').date()
                except ValueError:
                    payment_date = datetime.now().date()
            else:
                payment_date = datetime.now().date()
            
            # 处理账号
            account = default_account
            if account_index >= 0 and account_index < len(row) and row[account_index].strip():
                account = row[account_index]
            
            # 处理摘要
            summary = "批量退费"
            if summary_index >= 0 and summary_index < len(row) and row[summary_index].strip():
                summary = row[summary_index]
            
            # 解析班级名称以获取年级、年度和学期
            parts = class_name.split()
            if len(parts) != 3:
                error_count += 1
                continue
                
            grade, year, semester = parts
            
            # 查找学生注册记录
            registration = Registration.query.filter_by(
                student=student_name,
                grade=grade,
                year=year,
                semester=semester
            ).first()
            
            if not registration:
                error_count += 1
                continue
            
            # 创建退费记录
            payment = Payment(
                registration_id=registration.id,
                student=student_name,
                class_name=class_name,
                year=year,
                semester=semester,
                grade=grade,
                project=project_name,
                amount=amount,  # 负数金额
                date=payment_date,
                account=account,
                summary=summary
            )
            db.session.add(payment)
            success_count += 1
            
            # 更新汇总记录
            summary_record = PaymentSummary.query.filter_by(registration_id=registration.id).first()
            if not summary_record:
                summary_record = PaymentSummary(
                    registration_id=registration.id,
                    student=student_name,
                    class_name=class_name
                )
                db.session.add(summary_record)
            
            # 更新项目金额
            if not hasattr(summary_record, project_name):
                setattr(summary_record, project_name, amount)
            else:
                current_amount = getattr(summary_record, project_name) or 0
                setattr(summary_record, project_name, current_amount + amount)
        
        db.session.commit()
        flash(f'成功导入{success_count}条退费记录，失败{error_count}条', 'success' if success_count > 0 else 'warning')
    
    except Exception as e:
        db.session.rollback()
        flash(f'导入失败：{str(e)}', 'error')
    
    return redirect(url_for('list_payments'))

@app.route('/refund/template')
def download_refund_template():
    """下载退费记录CSV模板"""
    # 创建CSV文件
    si = StringIO()
    writer = csv.writer(si)
    
    # 写入BOM标记，确保Excel正确识别UTF-8编码
    si.write('\ufeff')
    
    # 写入表头和示例数据
    writer.writerow(['学生姓名', '班级名称', '退费项目', '退费金额', '退费日期', '退款账号', '摘要'])
    writer.writerow(['张三', '小班 2023 春季', '学费', '-1000', '2023-06-01', '现金', '退学退费'])
    writer.writerow(['李四', '中班 2023 春季', '杂费', '-200', '2023-06-02', '微信', '材料费退款'])
    
    # 生成响应
    output = si.getvalue()
    response = make_response(output)
    response.headers["Content-Disposition"] = "attachment; filename=refund_template.csv"
    response.headers["Content-type"] = "text/csv; charset=utf-8-sig"
    
    return response

@app.route('/import')
def import_page():
    return render_template('import/index.html')

@app.route('/import/data', methods=['POST'])
def import_data():
    if 'file' not in request.files:
        flash('没有选择文件', 'error')
        return redirect(url_for('import_page'))
    
    file = request.files['file']
    
    if file.filename == '':
        flash('没有选择文件', 'error')
        return redirect(url_for('import_page'))
    
    import_handler = ImportHandler()
    if not import_handler.allowed_file(file.filename):
        flash('不支持的文件格式', 'error')
        return redirect(url_for('import_page'))
    
    success, message = import_handler.import_data(file)
    
    if success:
        flash(message, 'success')
    else:
        flash(message, 'error')
    
    return redirect(url_for('import_page'))

@app.route('/import/template')
def download_import_template():
    """下载导入模板"""
    # 创建一个Excel writer对象
    output = BytesIO()
    writer = pd.ExcelWriter(output, engine='openpyxl')
    
    # 年级表模板
    grades_df = pd.DataFrame({
        '名称': ['小班', '中班', '大班'],
        '排序': [1, 2, 3]
    })
    grades_df.to_excel(writer, sheet_name='grades', index=False)
    
    # 学期表模板
    semesters_df = pd.DataFrame({
        '名称': ['春季', '秋季'],
        '排序': [1, 2]
    })
    semesters_df.to_excel(writer, sheet_name='semesters', index=False)
    
    # 收费项目表模板
    projects_df = pd.DataFrame({
        '名称': ['学费', '材料费', '活动费'],
        '排序': [1, 2, 3]
    })
    projects_df.to_excel(writer, sheet_name='projects', index=False)
    
    # 收款账号表模板
    accounts_df = pd.DataFrame({
        '账号': ['现金', '微信', '支付宝']
    })
    accounts_df.to_excel(writer, sheet_name='accounts', index=False)
    
    # 家长表模板
    parents_df = pd.DataFrame({
        '姓名': ['张三', '李四'],
        '电话': ['13800138000', '13900139000']
    })
    parents_df.to_excel(writer, sheet_name='parents', index=False)
    
    # 学生表模板
    students_df = pd.DataFrame({
        '姓名': ['张小明', '李小红'],
        '性别': ['男', '女'],
        '出生日期': ['2018-01-01', '2018-02-02'],
        '身份证号': ['110101201801011234', '110101201802022345'],
        '地址': ['北京市海淀区xxx', '北京市朝阳区xxx'],
        '家长姓名': ['张三', '李四'],
        '家长电话': ['13800138000', '13900139000'],
        '与家长关系': ['父子', '母女']
    })
    students_df.to_excel(writer, sheet_name='students', index=False)
    
    # 班级表模板
    classes_df = pd.DataFrame({
        '年级': ['小班', '中班'],
        '年份': ['2023', '2023'],
        '学期': ['春季', '春季']
    })
    classes_df.to_excel(writer, sheet_name='classes', index=False)
    
    # 注册表模板
    registrations_df = pd.DataFrame({
        '学生姓名': ['张小明', '李小红'],
        '班级年级': ['小班', '中班'],
        '班级年份': ['2023', '2023'],
        '班级学期': ['春季', '春季']
    })
    registrations_df.to_excel(writer, sheet_name='registrations', index=False)
    
    # 缴费表模板
    payments_df = pd.DataFrame({
        '日期': ['2023-03-01', '2023-03-01'],
        '学生姓名': ['张小明', '李小红'],
        '班级年级': ['小班', '中班'],
        '班级年份': ['2023', '2023'],
        '班级学期': ['春季', '春季'],
        '项目': ['学费', '学费'],
        '金额': [5000, 5000],
        '账号': ['微信', '支付宝'],
        '摘要': ['2023春季学费', '2023春季学费']
    })
    payments_df.to_excel(writer, sheet_name='payments', index=False)
    
    # 保存Excel文件
    writer.close()
    output.seek(0)
    
    return send_file(
        output,
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        as_attachment=True,
        download_name='import_template.xlsx'
    )

if __name__ == '__main__':
    app.run(debug=True)